package com.gmrz.uap.model;

import com.gmrz.uap.cfg.SystemConst;
import com.gmrz.uap.common.ConditionConst;
import com.gmrz.uap.common.DBCommon;
import com.gmrz.uap.model.base.BaseAuthenticators;
import com.gmrz.uap.util.DateUtil;
import com.gmrz.uap.util.Encodes;
import com.jfinal.kit.Kv;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.SqlPara;
import org.apache.commons.codec.binary.Hex;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.joda.time.DateTime;

import java.security.MessageDigest;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * Generated by JFinal.
 */
@SuppressWarnings("serial")
public class Authenticators extends BaseAuthenticators<Authenticators> {
    public static final Authenticators dao = new Authenticators();

    private static final Logger LOG = LogManager.getLogger(Authenticators.class);

    private static final String NAMESPACE = "authenticators";
    private static final String SQL_SELECT_COMM = NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "select_comm";
    private static final String SQL_SELECT_COUNT_DATE_TENANTID = NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "getLogOutCountByDate";
    private static final String SQL_SELECT_REG_COUNT_DATE_TENANTID = NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "getRegCountByDate";
    private static final String SQL_SELECT_PHONE_COUNT_DATE_TENANTID = NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "getPhoneCountByDate";


    /**
     * 根据日期获取当前月份注册的指纹数量
     *
     * @param date
     * @return
     */
    public Integer getRegCountByDate(String date, String tenantId, String authType) {
//        Kv cond = Kv.by("date", date).set("tenantId", tenantId).set("authType", authType);
//        SqlPara sqlPara = Db.getSqlPara(SQL_SELECT_REG_COUNT_DATE_TENANTID, cond);

        String sql = "SELECT count(*) FROM t_authenticators WHERE createts >= ? and createts < ? AND tenant_id = ? AND status <> 0 and AUTH_TYPE = ?";
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date d1 = null;
        try {
            d1 = simpleDateFormat.parse(date);
        } catch (ParseException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return Db.queryNumber(sql, d1, new Date(d1.getTime() + (long) 86400000), tenantId, authType).intValue();
    }

    /**
     * 根据日期获取当前月份注销的指纹数量
     *
     * @param date
     * @return
     */
    public Integer getLogOutCountByDate(String date, String tenantId, String authType) {
//        Kv cond = Kv.by("date", date).set("tenantId", tenantId).set("authType", authType);
//        SqlPara sqlPara = Db.getSqlPara(SQL_SELECT_COUNT_DATE_TENANTID, cond);
        String sql = "SELECT count(*) FROM t_authenticators WHERE updatets >= ? and updatets < ? AND tenant_id = ? AND status <> 1 and AUTH_TYPE = ?";

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date d1 = null;
        try {
            d1 = simpleDateFormat.parse(date);
        } catch (ParseException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }

        return Db.queryNumber(sql, new Date(d1.getTime()), new Date(d1.getTime() + (long) 86400000), tenantId, authType).intValue();
    }

    /**
     * 根据日期获取当前月份注销的指纹数量
     *
     * @param date
     * @return
     */
    public int getPhoneCountByDate(String date, String tenantId, String aaid) {
        String sql = "SELECT count(*) FROM t_authenticators WHERE createts >= ? and createts < ? AND tenant_id = ? AND aaid = ? AND  status <> 0";
//        Kv cond = Kv.by("date", date).set("tenantId", tenantId).set("aaid", aaid);
//        SqlPara sqlPara = Db.getSqlPara(SQL_SELECT_PHONE_COUNT_DATE_TENANTID, cond);
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date d1 = null;
        try {
            d1 = simpleDateFormat.parse(date);
        } catch (ParseException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return Db.queryNumber(sql, new Date(d1.getTime()), new Date(d1.getTime() + (long) 86400000), tenantId, aaid).intValue();
    }

    /**
     * 根据页面传过来的条件进行分页查询
     *
     * @param authenticators 元数据信息
     * @param page           页号
     * @param rows           行数
     * @param sort           排序字段
     * @param order          升序/降序
     * @return　分页结果 Page<AuthMetadata>
     */
    public Page<Authenticators> findListPaginate(Authenticators authenticators, int page, int rows, String sort, String order) {

        authenticators.put(SystemConst.DATATABLE_PAGINATE_OFFSET, page);
        authenticators.put(SystemConst.DATATABLE_PAGINATE_ROWS, rows);
        authenticators.put(SystemConst.DATATABLE_PAGINATE_SORT, sort);
        authenticators.put(SystemConst.DATATABLE_PAGINATE_ORDER, order);
        authenticators.put(SystemConst.DATATABLE_MAX_PAGE_COUNT_KEY, SystemConst.DATATABLE_MAX_PAGE_COUNT);

        //获取总行数
        SqlPara recordCountSql = Db.getSqlPara(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "findListForPaginateCount", authenticators);
        int totalRow = Db.queryInt(recordCountSql.getSql());
        //查询到的数量小于30000则使用查询到的结果
        totalRow = (totalRow + 1) < SystemConst.DATATABLE_MAX_PAGE_COUNT ? totalRow : SystemConst.DATATABLE_MAX_PAGE_COUNT;

        //获取数据集合
        SqlPara recordListSql = this.getSqlPara(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "findListForPaginate", authenticators);
        List<Authenticators> authenticatorsList = this.find(recordListSql);

        //获取总页数
        int totalPage = SystemConst.getTotalPage(totalRow, rows);

        //分页数据
        return new Page<Authenticators>(authenticatorsList, rows, page, totalPage, totalRow);
    }




    /**
     * 根据页面传过来的条件进行 证书使用明细 分页查询
     *
     * @param authenticators 元数据信息
     * @param page           页号
     * @param rows           行数
     * @param sort           排序字段
     * @param order          升序/降序
     * @return　分页结果 Page<AuthMetadata>
     */
    public Page<Authenticators> findCertUseDetaulList(Authenticators authenticators, int page, int rows, String sort, String order) {

        authenticators.put(SystemConst.DATATABLE_PAGINATE_OFFSET, page);
        authenticators.put(SystemConst.DATATABLE_PAGINATE_ROWS, rows);
        authenticators.put(SystemConst.DATATABLE_PAGINATE_SORT, sort);
        authenticators.put(SystemConst.DATATABLE_PAGINATE_ORDER, order);
        authenticators.put(SystemConst.DATATABLE_MAX_PAGE_COUNT_KEY, SystemConst.DATATABLE_MAX_PAGE_COUNT);
        // 获取机构号
        getCertDetailInstCodes(authenticators);

        //获取总行数
        SqlPara recordCountSql = Db.getSqlPara(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "findCertUseDetailCount", authenticators);
        int totalRow = Db.queryInt(recordCountSql.getSql());
        //查询到的数量小于30000则使用查询到的结果
        //totalRow = (totalRow + 1) < SystemConst.DATATABLE_MAX_PAGE_COUNT ? totalRow : SystemConst.DATATABLE_MAX_PAGE_COUNT;

        //获取数据集合
        SqlPara recordListSql = this.getSqlPara(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "findCertUseDetailList", authenticators);
        List<Authenticators> authenticatorsList = this.find(recordListSql);

        //获取总页数
        int totalPage = SystemConst.getTotalPage(totalRow, rows);

        //分页数据
        return new Page<Authenticators>(authenticatorsList, rows, page, totalPage, totalRow);
    }


    /**
     * 证书使用明细导出的数量
     * @param authenticators
     * @return
     */
    public int exportCertUseDetaulCount(Authenticators authenticators){
        // 获取机构号
        getCertDetailInstCodes(authenticators);

        //获取总行数
        SqlPara recordCountSql = Db.getSqlPara(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "exportCertUseDetailCount", authenticators);
        int totalRow = Db.queryInt(recordCountSql.getSql());
        return totalRow;
    }


    public List<Authenticators> exportCertUseDetaulList(Authenticators authenticators){
        // 获取机构号
        getCertDetailInstCodes(authenticators);

        //获取数据集合
        SqlPara recordListSql = this.getSqlPara(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "exportCertUseDetailList", authenticators);
        List<Authenticators> authenticatorsList = this.find(recordListSql);
        return authenticatorsList;
    }



    public void getCertDetailInstCodes(Authenticators authenticators){
        // 机构号可能会是多个，使用英文逗号隔开了
        String instCode = authenticators.get(ConditionConst.CONDITION_INST_CODE);
        String[] instCodes = null;
        if (instCode != null && instCode.contains(",")){
            instCodes = instCode.split(",");
            authenticators.put("inst_codes", instCodes);
        }else {
            authenticators.put("inst_codes", instCode);
        }
    }





    /**
     * 获取表中最小的时间
     *
     * @return
     */
    public DateTime getEarliestDate() {
        DateTime dateTime;
        String minDate = Db.queryStr(this.getSql(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "getEarliestDate"));

        if (StrKit.isBlank(minDate)) {//如果返回为空代表数据库中还没有数据，则返回当前时间
            dateTime = new DateTime();
        } else {
            dateTime = new DateTime(minDate);
        }
        return dateTime;
    }

    public int updateAuthenticatorStatus(Date date) {
        return Db.update(" UPDATE t_authenticators set status = 5 WHERE status = 1 and updatets < ?", new Date(date.getTime()));
    }

    public DateTime getCertEarliestDate() {
        DateTime dateTime;
        String minDate = Db.queryStr(this.getSql(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "getCertEarliestDate"));

        //如果返回为空代表数据库中还没有数据，则返回当前时间
        if (StrKit.isBlank(minDate)) {
            dateTime = new DateTime();
        } else {
            dateTime = new DateTime(minDate);
        }
        return dateTime;
    }

    public List<Record> getCertDeviceTypeStatisticBeanList(String date, List<String> aaidList) {
        StringBuffer stringBuffer = new StringBuffer();
        for (int i = 0; i < aaidList.size(); i++) {
            stringBuffer.append("'");
            stringBuffer.append(aaidList.get(i));
            stringBuffer.append("'");
            if (i != aaidList.size() - 1) {
                stringBuffer.append(",");
            }
        }
        String sql = "SELECT a.tenant_id,a.aaid,b.device_type,b.device_name,count(a.auth_id) statisticsCount \n" +
                "from t_authenticators a left join t_devices b on a.user_name=b.user_name \n" +
                "and a.device_id=b.device_id and a.auth_type=b.auth_type  and a.trans_type=b.trans_type and a.tenant_id = b.tenant_id \n" +
                "where a.status <> 0 and a.createts >= ? and a.createts < ? and a.aaid in(" + stringBuffer.toString() + ") GROUP BY a.tenant_id,a.aaid,b.device_type,b.device_name";

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date d1 = null;
        try {
            d1 = simpleDateFormat.parse(date);
        } catch (ParseException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }

        return Db.find(sql, new Date(d1.getTime()), new Date(d1.getTime() + (long) 86400000));
    }

    public int updateCertExtend(String ids, int from, int to) {
        String sql = "UPDATE t_certificate_extend set cert_status = ? WHERE cert_status = ? and auth_id = ?";
        return Db.update(sql, to, from, ids);
    }



    public List<Record> getCertUseCaseStatisticBeanList(String date) {

        String sql = "select aaid, inst_code, count(crt_id) statisticsCount from \n" +
                "(select tc.crt_id, tc.auth_id, tc.tenant_id, tc.aaid , tc.createts, ta.inst_code  \n" +
                "from(select * from t_certificate  where is_repeat = '0' and createts >= ? and createts < ? )tc \n" +
                "INNER JOIN  t_authenticators ta on tc.auth_id = ta.auth_id )a group by aaid, inst_code";

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date d1 = null;
        try {
            d1 = simpleDateFormat.parse(date);
        } catch (ParseException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }

        return Db.find(sql, new Date(d1.getTime()), new Date(d1.getTime() + (long) 86400000));
    }
}











